USE [Estoque]
GO

/****** Object:  StoredProcedure [dbo].[usp_RelatorioNecessidades]    Script Date: 10/24/2010 23:30:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[usp_RelatorioNecessidades]
	@DataIni datetime,
	@DataFim datetime
AS
begin

Select p.Id, p.Nome, p.Estoque_Minimo - ISnull(Sum(e.Qtde),0) as Qtde, isnull(cast(pc.DT_Pedido as varchar(10)),'') as DT_Pedido from Produtos p
left join Estoque e 
on e.Produto_Id = p.Id and e.Dt_Operacao between @DataIni and @DataFim
left join 
(select top 1 pcp.Id, pc.Dt_Pedido from Pedidos_Compra pc
inner join Pedido_Compra_Produtos pcp
on pcp.Pedido_Id = pc.Id
where pc.Dt_Pedido between  @DataIni and @DataFim
order by pc.Dt_Pedido Desc) pc
on pc.Id = p.Id
where p.Estoque_Minimo > isnull(e.Qtde,0)
group by p.Id,p.Nome,p.Estoque_Minimo,pc.DT_Pedido
end
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[usp_RelatorioEstoque]
	@DataIni datetime,
	@DataFim datetime
AS
begin

Select p.Id, p.Nome, ISnull(Sum(e.Qtde),0) as Qtde,isnull(Sum(pcp.Qtde),0) as QtdeSolicitada from Produtos p
left join Estoque e 
on e.Produto_Id = p.Id and e.Dt_Operacao between @DataIni and @DataFim
left join Pedido_Compra_Produtos pcp
on pcp.Produto_Id = p.Id
left join Pedidos_Compra pc 
on pc.Id = pcp.Pedido_Id and pc.[Status] = 'C'and pc.Dt_Pedido between @DataIni and @DataFim
group by p.Id,p.Nome

end

GO

/****** Object:  View [dbo].[vw_RelatorioNecessidades]    Script Date: 10/24/2010 23:30:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE view [dbo].[vw_RelatorioNecessidades]
as

Select 0 as Id, '' as Nome, 0 as Qtde, '' as DT_Pedido 

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE view [dbo].[vw_RelatorioEstoque]
as

Select 0 as Id,'' as Nome, 0 as Qtde,0 as QtdeSolicitada 


GO
